/**
 * 
 */
package org.iridium.allcomments.persistance;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author iridium
 * 
 */
public class UserInfoDAOImpl extends AbstractSystemDAO implements UserInfoDAO {

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.iridium.allcomments.persistance.SysUserMDAO#addItem(java.lang.String,
	 * java.lang.String, int)
	 */
	@Override
	public void addItem(long userId, String loginName, String password,
			String email, String nickname, int roleId) {
		this.buildConnection();
		String sql = "insert into user_info(id, login_name, password, email, nickname, role_id) values(?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, userId);
			ps.setString(2, loginName);
			ps.setString(3, password);
			ps.setString(4, email);
			ps.setString(5, nickname);
			ps.setInt(6, roleId);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		this.closeConnection();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.iridium.allcomments.persistance.SysUserDDAO#addItem(java.lang.String,
	 * java.lang.String, java.lang.String, int, int, java.lang.String,
	 * java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public void addItem(long userId, String nickname, String realName, int sex,
			int age, String location, String question, String answer,
			String mobile, String email) {
		this.buildConnection();
		String sql = "insert into sys_user_d(user_id, nickname, realname, sex, age, location, question, answer, mobile, email_contact) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, userId);
			ps.setString(2, nickname);
			ps.setString(3, realName);
			ps.setInt(4, sex);
			ps.setInt(5, age);
			ps.setString(6, location);
			ps.setString(7, question);
			ps.setString(8, answer);
			ps.setString(9, mobile);
			ps.setString(10, email);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		this.closeConnection();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.iridium.allcomments.persistance.SysUserMDAO#delItem(java.lang.String)
	 */
	@Override
	public void delItem(long userId) {
		this.buildConnection();
		String sql = "delete from user_info where user_id = ?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, userId);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		this.closeConnection();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.iridium.allcomments.persistance.SysUserMDAO#updatePassword(java.lang
	 * .String, java.lang.String)
	 */
	@Override
	public void updatePassword(long userId, String password) {
		this.buildConnection();
		String sql = "update sys_user_m set password = ? where user_id = ?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, password);
			ps.setLong(2, userId);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		this.closeConnection();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.iridium.allcomments.persistance.SysUserMDAO#updateRoleId(java.lang
	 * .String, int)
	 */
	@Override
	public void updateRoleId(long userId, int roleId) {
		this.buildConnection();
		String sql = "update sys_user_m set role_id = ? where user_id = ?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, roleId);
			ps.setLong(2, userId);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		this.closeConnection();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.iridium.allcomments.persistance.SysUserDDAO#updateAge(java.lang.String
	 * , int)
	 */
	@Override
	public void updateAge(long userId, int age) {
		this.updateObject(userId, "age", age);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.iridium.allcomments.persistance.UserInfoDAO#updateEmail(java
	 * .lang.String, java.lang.String)
	 */
	@Override
	public void updateEmail(long userId, String email) {
		this.updateObject(userId, "email", email);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.iridium.allcomments.persistance.UserInfoDAO#updateLocation(java.lang
	 * .String, java.lang.String)
	 */
	@Override
	public void updateLocation(long userId, String location) {
		this.updateObject(userId, "location", location);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.iridium.allcomments.persistance.UserInfoDAO#updateSex(java.lang.String
	 * , int)
	 */
	@Override
	public void updateSex(long userId, int sex) {
		this.updateObject(userId, "sex", sex);
	}

	@Override
	public void updateAnswer(long userId, String answer) {
		this.updateObject(userId, "answer", answer);
	}

	@Override
	public void updateMobile(long userId, String mobile) {
		this.updateObject(userId, "mobile", mobile);
	}

	@Override
	public void updateNickname(long userId, String nickname) {
		this.updateObject(userId, "nickname", nickname);
	}

	@Override
	public void updateQuestion(long userId, String question) {
		this.updateObject(userId, "question", question);
	}

	@Override
	public void updateRealName(long userId, String realName) {
		this.updateObject(userId, "realname", realName);
	}

	@Override
	public void updateItem(long userId, String nickname, String realName,
			int sex, int age, String location, String question, String answer,
			String mobile, String email) {
		this.buildConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("update user_info					");
		sql.append("   set nickname                 = ?,");
		sql.append("       realname                 = ?,");
		sql.append("       sex                      = ?,");
		sql.append("       age                      = ?,");
		sql.append("       location                 = ?,");
		sql.append("       question                 = ?,");
		sql.append("       answer                   = ?,");
		sql.append("       mobile                   = ?,");
		sql.append("       email                    = ?,");
		sql.append(" where user_id = ?                  ");
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, nickname);
			ps.setString(2, realName);
			ps.setInt(3, sex);
			ps.setInt(4, age);
			ps.setString(5, location);
			ps.setString(6, question);
			ps.setString(7, answer);
			ps.setString(8, mobile);
			ps.setString(9, email);
			ps.setLong(12, userId);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		this.closeConnection();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.iridium.allcomments.persistance.SysUserMDAO#existsUser(java.lang.
	 * String)
	 */
	@Override
	public boolean existsUser(long userId) {
		this.buildConnection();
		String sql = "SELECT ID FROM USER_INFO WHERE ID=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		long userQry = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, userId);
			rs = ps.executeQuery();
			while (rs.next()) {
				userQry = rs.getLong(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		this.closeConnection();
		return userId == userQry;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.iridium.allcomments.persistance.UserInfoDAO#queryPassword(java.lang
	 * .String)
	 */
	@Override
	public String queryPassword(long userId) {
		this.buildConnection();
		String sql = "select password from sys_user_m where user_id = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		String password = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, userId);
			rs = ps.executeQuery();
			while (rs.next()) {
				password = rs.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		this.closeConnection();
		return password;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.iridium.allcomments.persistance.UserInfoDAO#queryRoleId(java.lang
	 * .String)
	 */
	@Override
	public int queryRoleId(long userId) {
		this.buildConnection();
		String sql = "select role_id from sys_user_m where user_id = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		int roleId = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, userId);
			rs = ps.executeQuery();
			while (rs.next()) {
				roleId = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		this.closeConnection();
		return roleId;
	}

	/**
	 * 
	 * @param userId
	 *            the key of table 'sys_user_d'
	 * @param paramName
	 *            the column name of the table in RDBMS
	 * @param value
	 */
	private void updateObject(long userId, String paramName, Object value) {
		this.buildConnection();
		String sql = "update sys_user_d set " + paramName
				+ " = ? where user_id = ?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, value);
			ps.setLong(2, userId);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		this.closeConnection();
	}
}
